/**
 * Copyright (c) 2007,中企动力华南研发中心<br>
 * All rights reserved.<br>
 */
package com.ce.pms.charge.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ce.pms.base.service.impl.PmsBaseServiceImpl;
import com.ce.pms.charge.dao.IBankCollectionDao;
import com.ce.pms.charge.dao.IBusinessCodeDao;
import com.ce.pms.charge.dao.IChargeVoucherDao;
import com.ce.pms.charge.dao.ICollecDetailDao;
import com.ce.pms.charge.dao.IDebitTransDao;
import com.ce.pms.charge.dao.IFeeBankDao;
import com.ce.pms.charge.dao.IPeriodDao;
import com.ce.pms.charge.dao.ISubAccountDao;
import com.ce.pms.charge.dao.ITransactionDao;
import com.ce.pms.charge.dao.ITransactionRelationDao;
import com.ce.pms.charge.model.AbstractTrans;
import com.ce.pms.charge.model.BankDomain;
import com.ce.pms.charge.model.BusinessCodeDomain;
import com.ce.pms.charge.model.CollecDetail;
import com.ce.pms.charge.model.CollectionFileDomain;
import com.ce.pms.charge.model.CreditTransDomain;
import com.ce.pms.charge.model.DebitTransDomain;
import com.ce.pms.charge.model.PeriodDomain;
import com.ce.pms.charge.model.VoucherDomain;
import com.ce.pms.charge.service.IBankCollectionService;
import com.ce.pms.charge.vo.CollectionDetailVO;
import com.ce.pms.charge.vo.CollectionFileVO;
import com.ce.pms.charge.vo.SubAccountBean;
import com.ce.pms.charge.vo.TransactionRelationBean;
import com.ce.pms.common.model.SysInfo;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.pms.common.util.UpLoadFile;
import com.ce.pms.customer.model.ProRightDO;
import com.ce.pms.exception.BizLayerException;
import com.ce.pms.exception.BusinessException;
import com.ce.pms.house.dao.IHouseDao;
import com.ce.pms.house.model.HouseDomain;
import com.ce.util.DateUtil;
import com.ce.util.StringUtil;
import com.sitechasia.webx.core.support.Page;

/**
 * 功能描述：银行托收业务类,主要业务有银行出盘、回盘<br>
 * @author 易贤建 创建日期：2008-9-22<br>
 */
public class BankCollectionServiceImpl extends PmsBaseServiceImpl implements
		IBankCollectionService {

	private IBankCollectionDao bankCollectionDao = null;
	private ISubAccountDao  subAccountDao = null;//子帐户
	private IFeeBankDao feeBankDao = null; //划款银行
	private IPeriodDao periodDao = null; // 收费周期
	private IDebitTransDao debitTransDao = null;
	private ICollecDetailDao collecDetailDao=null;
	private IChargeVoucherDao chargeVoucherDao=null;
	private ITransactionDao transactionDao=null;
	private IBusinessCodeDao businessCodeDao=null;
	private ITransactionRelationDao transactionRelationDao=null;
	private IHouseDao houseDao=null;

	private final String OUTFILEPATH = "fee_collection_out_file_path"; //银行出盘路径
	private final String INFILEPATH = "fee_collection_in_file_path"; //银行回盘路径
	private final String PREPAY_VOUCHER="JFT";//预交款凭证前缀

	private Integer voucherSeq=0;//收费凭证流水号
	/**
	 * 功能描述:  查询银行出盘列表<br>
	 * @param collectonfilevo
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-22  <br>
	 */
	public Page queryOutBankCollection(CollectionFileVO collectonfilevo) {
		Page page = bankCollectionDao.queryOutBankCollection(collectonfilevo);
		page.setResult(convertDoToVO(page.getResult()));
		return page;
	}
	/**
	 * 功能描述:  查询银行回盘列表<br>
	 * @param collectonfilevo
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-22  <br>
	 */
	public Page queryInBankCollection(CollectionFileVO collectonfilevo){
		Page page = bankCollectionDao.queryInBankCollection(collectonfilevo);
		page.setResult(convertDoToVO(page.getResult()));
		return page;
	}

	/**
	 * 功能描述:查询通知单明细.托收ID必须填<br>
	 * @param searchVO
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-14<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public Page queryCollectionDetail(CollectionDetailVO searchVO){
		checkParameterIsNull(searchVO.getCollectionId(), "charge:collectionId_not_null_exception");

		Page page=collecDetailDao.queryCollectionDetail(searchVO);
		page.setResult(convertDetailDoToVoList(page.getResult()));
		return page;
	}

	/**
	 * 功能描述:查找指定的托收信息<br>
	 * @param collectionId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-14<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public CollectionFileVO findCollectionFile(Long collectionId){
		CollectionFileDomain collectionFileDomain=bankCollectionDao.getCollectionFileById(collectionId);
		return convertDoToVO(collectionFileDomain);
	}

	/**
	 * 功能描述:  银行出盘<br>
	 * 出盘步骤：1>获取所有子帐户,查询条件为：先获取指定银行ID的有效的银行划款帐号,
	 * 								 根据银行划款帐号的房屋ID,产权ID获取对应的有效的帐户
	 * 								 根据帐户获取所有的有效的物业子帐户
	 *          2>添加一条银行托收文件主记录
	 *          3>根据第一步查询的返回的结果,添加当前银行托收文件托收明细
	 *          4>更新物业子帐户交易,将所有未缴费记录更新为出盘锁定状态.
	 * @param bandId
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-23  <br>
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public synchronized void out(Long bandId,Long createor) throws Exception {
		checkParamNotNum(bandId,"charge:charege_collection_out_bankId_notNull");
		checkBankIsOutAndNotIn(bandId);//如果已经出盘,但还未回盘, 则不能再次出盘
		//根据银行ID  获取有效的划款帐户对应的帐户的余额大于0的子帐户,及对应的帐户,划款银行帐号
		//每项为一个Object数组,1-房屋ID,2--房号,3--产权ID,4--银行账号,5--开户人,6--子帐户余额,银行帐户ID
		List chargeAccountList =subAccountDao.findSubAccountByBankId(bandId); //查询产权房
		List chargeAccountWithVirtual = subAccountDao.findSubAccountWithVirtualHouseByBankId(bandId);//查询管理房
		if(chargeAccountWithVirtual != null && chargeAccountWithVirtual.size() > 0)
			chargeAccountList.addAll(chargeAccountWithVirtual);
		if(chargeAccountList == null || chargeAccountList.size() == 0)
			throw new BusinessException("charge:charge_collection_out_notOut",BusinessException.FAILURE);
		//添加银行出盘文件主记录
		CollectionFileDomain collectionFileDomain = addCoolectionFileDomain(bandId,createor);
		//添加托收明细
		System.out.println("明细开始:");
		Long startTime = System.currentTimeMillis();
		List<CollecDetail>  collecDetaillist = addCollecDetail(collectionFileDomain,chargeAccountList);
		Long endTime = System.currentTimeMillis();
		System.out.println("明细结束,用时---:" +  (endTime - startTime));

		System.out.println("更新子帐户开始:");
		startTime = System.currentTimeMillis();
		//更新子帐户交易, 将所有收费类型的未缴费的记录更新为出盘锁定状态，同时在托收明细和子帐户交易关系表中插入关系表中。
		updateTransaction(bandId,collectionFileDomain.getKeyword());
		endTime = System.currentTimeMillis();
		System.out.println("更新子帐户结束,用时---:" +  (endTime - startTime));

		System.out.println("生成文件开始:");
		startTime = System.currentTimeMillis();
		//创建银行出盘记录文件
		buildOutFile(collectionFileDomain,collecDetaillist);
		endTime = System.currentTimeMillis();
		System.out.println("生成文件结束,用时---:" +  (endTime - startTime));
	}
	/**
	 * 功能描述:根据出盘ID,获取出盘VO<br>
	 * @param keyword
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-10-7  <br>
	 */
	public CollectionFileVO getCollectionFileById(Long keyword){
		checkParameterIsNull(keyword, "charge:charge_bankCollection_id_notNull");
		CollectionFileDomain collectionFileDomain = bankCollectionDao.getCollectionFileById(keyword);
		return convertDoToVO(collectionFileDomain);
	}

	/**
	 * 功能描述:回盘<br>
	 * @param bankId
	 * @param outFileName
	 * @param createor
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-7<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public Boolean in(Long bankId,String inFileName,HSSFWorkbook inExcel,Long createor){
		Long startDate=System.currentTimeMillis();
		logger.info("开始回盘...");
		//缓存数据
		BusinessCodeDomain prePayBusinessCode = getBankPaidCode();
		checkParameterIsNull(prePayBusinessCode, "charge:payBussinessCode_not_null");
		voucherSeq=chargeVoucherDao.getMaxVoucherNumSeqByType(PREPAY_VOUCHER);//通知单序号
		List<TransactionRelationBean> transactionRelationList=new ArrayList<TransactionRelationBean>();
		Map<Long,SubAccountBean> houseSubAccount=subAccountDao.getAllSubAccount();
		SysInfo currentSysInfo=createInfo(createor);//当前用户信息
		//校验
		CollectionFileDomain collectionFileDomain=bankCollectionDao.getCollectionFileByInFileName(inFileName);
		checkParameterIsNull(collectionFileDomain, "charge:inFile_is_fail");
		Map<Long, CollectionDetailVO> collectionDetailMap = validateExcel(inExcel);
		if(collectionDetailMap.size()!=collectionFileDomain.getCollectionDetails().size()){
			throw new BusinessException("charge:inFile_count_not_same_db");
		}
		//处理
		synchronized (this) {
			for(CollecDetail item : collectionFileDomain.getCollectionDetails()){
				SubAccountBean subAccount=houseSubAccount.get(item.getHouse().getKeyword());
				checkSubAccount(item, subAccount);

				item.setStatuz( collectionDetailMap.get(item.getCollecDetailId()).getStatus() );
				if( !item.getStatuz().equals(SysrefcodeDO.FEE_COLLECTION_DETAIL_STATUZ_SUCCEED) ){
					continue;
				}

				VoucherDomain payVoucher = addVoucher(++voucherSeq, item, collectionFileDomain.getPeriod(), currentSysInfo);

				CreditTransDomain payTranys=addPayTrans(prePayBusinessCode, item, payVoucher, collectionFileDomain.getPeriod(),
						subAccount.getSubAccountId(), currentSysInfo);

				addTransactionRelation(item.getTransSet(), payTranys, transactionRelationList);
			}
			//更新状态
			logger.info("1:"+System.currentTimeMillis());
			updateIninfoCollection(inFileName, createor, collectionFileDomain);

			logger.info("1:"+System.currentTimeMillis());
			collecDetailDao.updateCollecDetailStatus(collectionDetailMap);

			logger.info("1:"+System.currentTimeMillis());
			transactionRelationDao.insertTransactionRelationBatch(transactionRelationList);

			logger.info("1:"+System.currentTimeMillis());
			transactionDao.updateTransactionStatus(wrapTransactionStatus(collectionFileDomain.getCollectionDetails(), houseSubAccount));

			logger.info("1:"+System.currentTimeMillis());
			subAccountDao.updateSubAccountBalanceBatchSub(wrapSubaccountBalance(collectionFileDomain.getCollectionDetails(), houseSubAccount));

			logger.info("1:"+System.currentTimeMillis());
			writeHSSFWorkbook(collectionFileDomain.getInFilePath(), collectionFileDomain.getInFileName(), inExcel);
		}
		logger.info("回盘成功，所花时间为："+(System.currentTimeMillis()-startDate));
		return true;
	}

	/**
	 * 功能描述:查看给定的银行帐号是否出盘(不考虑时间限制,包括历史、现在的,只要出过盘)<br>
	 * @param bankNumber ，银行帐号,不能为空,为空则抛bussnessException异常
	 * @return true--已出盘,false--没出过盘
	 * Create author:yixianjian <br>
	 * Create on:2008-10-24  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public boolean isOuted(String bankNumber){
		super.checkParameterIsNull(bankNumber, "charge:charge_collection_bankNumber_isOuted_bankNumberNull");
		long count = bankCollectionDao.getBankAccountCountFromCollectionDetail(bankNumber);
		return count!=0;
	}
	/**
	 * 功能描述:查询给定银行帐号是否回盘<br>
	 * @param bankNumber
	 * @return true 标识不存在没有回盘的数据,false标识存在未回盘的数据
	 * Create author:yixianjian <br>
	 * Create on:2008-10-24  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public boolean isIn(String bankNumber){
		super.checkParameterIsNull(bankNumber, "charge:charge_collection_bankNumber_isOuted_bankNumberNull");
		//如果银行帐号对应的托收明细状态有新建的,则表明有未回盘的数据
		long count = bankCollectionDao.getBankAccountCountFromCollectionDetail(bankNumber,SysrefcodeDO.FEE_COLLECTION_DETAIL_STATUZ_NEW);
		return count==0;
	}
	/**
	 * 功能描述:检查帐户的状态<br>
	 * @param item
	 * @param subAccount<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-14<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private void checkSubAccount(CollecDetail item, SubAccountBean subAccount) {
		if(null!=subAccount && subAccount.isLockedCredit()){
			HouseDomain house= houseDao.findById(item.getHouse().getKeyword());
			throw new BusinessException("charge:account_is_lock_not_in,"+house.getHouseNumber(),BusinessException.ERROR);
		}
		if(null==subAccount){
			HouseDomain house= houseDao.findById(item.getHouse().getKeyword());
			throw new BusinessException("charge:house_account_is_exists,"+house.getHouseNumber(),BusinessException.ERROR);
		}
	}


	/**
	 * 功能描述:封装帐户余额<br>
	 * @param collectionDetails
	 * @param houseSubAccount
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-8<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private Map<Long,BigDecimal> wrapSubaccountBalance(Set<CollecDetail> collectionDetails,Map<Long,SubAccountBean> houseSubAccount){
		Map<Long,BigDecimal> subAccountBalance=new HashMap<Long, BigDecimal>();
		for(CollecDetail detail : collectionDetails){
			if( detail.getStatuz().equals(SysrefcodeDO.FEE_COLLECTION_DETAIL_STATUZ_SUCCEED) ){
				subAccountBalance.put(houseSubAccount.get(detail.getHouse().getKeyword()).getSubAccountId(), detail.getAmt());
			}
		}
		return subAccountBalance;
	}

	/**
	 * 功能描述:封装交易状态<br>
	 * @param collectionDetails
	 * @param houseSubAccount
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-8<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private Map<Long,String> wrapTransactionStatus(Set<CollecDetail> collectionDetails,Map<Long,SubAccountBean> houseSubAccount){
		Map<Long,String> status=new HashMap<Long, String>();
		for(CollecDetail detail : collectionDetails){
			if(SysrefcodeDO.FEE_COLLECTION_DETAIL_STATUZ_SUCCEED.equals(
					detail.getStatuz())){
				status.put(houseSubAccount.get(detail.getHouse().getKeyword()).getSubAccountId(), SysrefcodeDO.FEE_TRANSACTION_STATUZ_PAYED);
			}else{
				status.put(houseSubAccount.get(detail.getHouse().getKeyword()).getSubAccountId(), SysrefcodeDO.FEE_TRANSACTION_STATUZ_NOTPAY);
			}
		}
		return status;
	}

	/**
	 * 功能描述:增加关联交易<br>
	 * @param transSet
	 * @param payTranys
	 * @param transactionRelationList<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-8<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private void addTransactionRelation(Set<AbstractTrans> transSet,CreditTransDomain payTranys,List<TransactionRelationBean> transactionRelationList){
		for(AbstractTrans tran : transSet){
			if ( isSingleLatefee(transSet, ((DebitTransDomain)tran).getMTransId())
					|| SysrefcodeDO.FEE_TRANSACTION_CLAZZ_PRINCIPAL.equals(tran.getClazz())) {
				TransactionRelationBean transactionRelationBean=new TransactionRelationBean();
				transactionRelationBean.setMasterTransactionId(payTranys.getTransactionId());
				transactionRelationBean.setRelationTransactionid(tran.getTransactionId());
				transactionRelationBean.setRelationType(SysrefcodeDO.FEE_TRA_R_TRA_RELATION_TYPE_PAY);
				transactionRelationList.add(transactionRelationBean);

			}
		}
	}

	/**
	 * 功能描述:判断交易是否是单独的滞纳金交易<br>
	 * @param transSet
	 * @param tranId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-11-21<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private boolean isSingleLatefee(Set<AbstractTrans> transSet,Long tranId){
		for(AbstractTrans item : transSet){
			if(tranId.equals(item.getTransactionId())){
				return false;
			}
		}
		return true;
	}

	/**
	 * 功能描述:更新回盘信息。回盘文件名、路径名、回盘时间、回盘人<br>
	 * @param inFileName
	 * @param createor
	 * @param collectionFileDomain<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-8<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private void updateIninfoCollection(String inFileName, Long createor, CollectionFileDomain collectionFileDomain) {
		collectionFileDomain.setInFileName(inFileName);
		collectionFileDomain.setInFilePath(getPath(INFILEPATH));
		collectionFileDomain.setInMan(createor);
		collectionFileDomain.setInTime(this.getDate());
	}

	/**
	 * 功能描述:对EXCEL文件进行基础性的验证。只要有一行数据异常将停止并抛出异常信息。<br>
	 * 验证规则有：
	 * <ol>
	 * <li> ID(唯一性标示号)不能为空或是否是整数类型且是否唯一。</li>
	 * <li> 回盘文件的前缀是否和指定银行的代码一致。</li>
	 * <li> 文件中划款状态的值是否是‘Y’或‘N’，不存在未划款的数据。</li>
	 * <li> 金额不能为负数。</li>
	 * </ol>
	 * @param inExcel
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-8<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private Map<Long, CollectionDetailVO> validateExcel(HSSFWorkbook inExcel) {
		Map<Long, CollectionDetailVO> collectionDetailMap=convertExcelToMap(inExcel);
		for(CollectionDetailVO bankOutInBean : collectionDetailMap.values()){
			if(!collecDetailDao.checkExcelDataIsSameDB(bankOutInBean)){
				throw new BusinessException("charge:inFile_data_not_same_db",BusinessException.ERROR);
			}
		}
		return collectionDetailMap;
	}
	/**
	 * 功能描述:<br>
	 * @param inExcel
	 * @param collectionDetailMap<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-8<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private Map<Long, CollectionDetailVO>  convertExcelToMap(HSSFWorkbook inExcel) {
		 Map<Long, CollectionDetailVO> collectionDetailMap=new HashMap<Long, CollectionDetailVO>();
		for( int rowNum=1;rowNum<=inExcel.getSheetAt(0).getLastRowNum();rowNum++ ){
			CollectionDetailVO bankOutInBean = convertRowToObject(inExcel.getSheetAt(0).getRow(rowNum));
			if(collectionDetailMap.containsKey(bankOutInBean.getCollectionDetailId())){
				throw new BusinessException("charge:inFile_id_has_same",BusinessException.ERROR);
			}
			collectionDetailMap.put(bankOutInBean.getCollectionDetailId(), bankOutInBean);

		}
		return collectionDetailMap;
	}
	/**
	 * 功能描述:<br>
	 * @param row
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-8<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private CollectionDetailVO convertRowToObject(HSSFRow row) {
		CollectionDetailVO bankOutInBean=new CollectionDetailVO();
		if( null!=row.getCell((short) 0) && row.getCell((short) 0).getCellType()== HSSFCell.CELL_TYPE_STRING ){
			bankOutInBean.setCollectionDetailId(new Long( row.getCell((short) 0).getRichStringCellValue().getString() ));
		}else{
			throw new BusinessException("charge:inFile_id_error",BusinessException.ERROR);
		}

		if( null!=row.getCell((short) 1) && row.getCell((short) 1).getCellType()== HSSFCell.CELL_TYPE_STRING ){
			bankOutInBean.setHouseNum( row.getCell((short) 1).getRichStringCellValue().getString() );
		}else{
			throw new BusinessException("charge:inFile_houseNumber_error",BusinessException.ERROR);
		}

		if( null!=row.getCell((short) 2) && row.getCell((short) 2).getCellType()== HSSFCell.CELL_TYPE_STRING ){
			bankOutInBean.setAccOwner( row.getCell((short) 2).getRichStringCellValue().getString() );
		}else{
			throw new BusinessException("charge:inFile_name_error",BusinessException.ERROR);
		}

		if( null!=row.getCell((short) 3) && row.getCell((short) 3).getCellType()== HSSFCell.CELL_TYPE_STRING ){
			bankOutInBean.setAccNumber( row.getCell((short) 3).getRichStringCellValue().getString() );
		}else{
			throw new BusinessException("charge:inFile_accNumber_error",BusinessException.ERROR);
		}

		if( null!=row.getCell((short) 4) && row.getCell((short) 4).getCellType()== HSSFCell.CELL_TYPE_STRING ){
			bankOutInBean.setAmt( new BigDecimal(row.getCell((short) 4).getRichStringCellValue().getString()) );
			if(null==bankOutInBean.getAmt() || BigDecimal.ZERO.compareTo( bankOutInBean.getAmt() )>=0){
				throw new BusinessException("charge:inFile_amt_error",BusinessException.ERROR);
			}
		}else{
			throw new BusinessException("charge:inFile_amt_error",BusinessException.ERROR);
		}

		if( null!=row.getCell((short) 5) && row.getCell((short) 5).getCellType()== HSSFCell.CELL_TYPE_STRING ){
			bankOutInBean.setStatus( row.getCell((short) 5).getRichStringCellValue().getString() );
			if( "Y".equals( bankOutInBean.getStatus())){
				bankOutInBean.setStatus(SysrefcodeDO.FEE_COLLECTION_DETAIL_STATUZ_SUCCEED);
			}else if("N".equals( bankOutInBean.getStatus())){
				bankOutInBean.setStatus(SysrefcodeDO.FEE_COLLECTION_DETAIL_STATUZ_FAIL);
			}else{
				throw new BusinessException("charge:inFile_status_Is_yn",BusinessException.ERROR);
			}

		}else if(null!=row.getCell((short) 5)){
			throw new BusinessException("charge:no_out_error",BusinessException.ERROR);
		}else{
			throw new BusinessException("charge:inFile_status_error",BusinessException.ERROR);
		}
		return bankOutInBean;
	}
	/**
	 * 功能描述:获取银行托收的交易码<br>
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-7<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private BusinessCodeDomain getBankPaidCode() {
		//ParameterDefDomain parameterDefDomain= parameterDefDao.findUniqueBy("pkey", SysrefcodeDO.BANK_PAID_CODE);
		BusinessCodeDomain prePayBusinessCode=businessCodeDao.findBusinessCodeByParaDef(SysrefcodeDO.BANK_PAID_CODE);
			//businessCodeDao.findById(parameterDefDomain.getPvalue());
		return prePayBusinessCode;
	}

	/**
	 * 功能描述: 新增一个收费凭证<br>
	 * @param voucherSeq
	 * @param temp
	 * @param currentPeriod
	 * @param userId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-28<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private VoucherDomain addVoucher(int voucherSeq, CollecDetail temp,PeriodDomain currentPeriod,SysInfo sysInfo){
		VoucherDomain payVoucher=new VoucherDomain();
		payVoucher.setHouse(temp.getHouse());
		payVoucher.setProRight(temp.getProRight());
		payVoucher.setVoucherNumber(buildFlowNumber(PREPAY_VOUCHER,currentPeriod.getChargePrecinct().getKeyword(),voucherSeq));
		payVoucher.setSum(temp.getAmt());
		payVoucher.setChargeDate(sysInfo.getSysDate());
		payVoucher.setStatuz(SysrefcodeDO.FEE_VOUCHER_STATUZ_NEW);
		payVoucher.setSysInfo(sysInfo);
		chargeVoucherDao.saveObject(payVoucher);
		return payVoucher;
	}

	/**
	 * 功能描述:新增支付交易<br>
	 * @param bankPaidCode
	 * @param temp
	 * @param payVoucher
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-28<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private CreditTransDomain addPayTrans(BusinessCodeDomain bankPaidCode, CollecDetail temp, VoucherDomain payVoucher,PeriodDomain period,Long chargeAccountId,SysInfo sysinfo) {
		checkParameterIsNull(chargeAccountId, "charge:house_account_is_exists,"+temp.getHouse().getHouseNumber());

		CreditTransDomain payTrans=new CreditTransDomain();
		payTrans.setBindingAccount(subAccountDao.loadById(chargeAccountId));
		payTrans.setCredit(temp.getAmt());
		payTrans.setClazz(SysrefcodeDO.FEE_TRANSACTION_CLAZZ_MAINPAY);
		payTrans.setBusinessCode(bankPaidCode);
		payTrans.setPeriod(period);
		payTrans.setVoucher(payVoucher);
		payTrans.setSysInfo(sysinfo);
		transactionDao.saveObject(payTrans);
		return payTrans;
	}

	/**
	 * 功能描述:检测相应银行是否已经出过盘<br>
	 * @param bankId 银行ID
	 * Create author:yixianjian <br>
	 * Create on:2008-10-7  <br>
	 */
	private void checkBankIsOutAndNotIn(Long bankId){
		CollectionFileDomain domain = bankCollectionDao.getCollectionFileDomainForMaxKeyword(bankId);

		if(domain != null && StringUtil.isEmpty(domain.getInFileName())){
			throw new BusinessException("charge:charge_collection_out_not_in",BusinessException.WARNING);
		}
	}
	/**
	 * 功能描述:  给定一个银行ID,在托收文件表中添加一条托收文件记录。<br>
	 * @param bandId
	 * Create author:yixianjian <br>
	 * Create on:2008-9-24  <br>
	 * @throws ParseException
	 */
	@SuppressWarnings("unchecked")
	private CollectionFileDomain addCoolectionFileDomain(Long bandId,Long createor) throws ParseException{
		CollectionFileDomain collectionFileDomain = new CollectionFileDomain();

		BankDomain bankDomain = feeBankDao.findById(bandId);
		PeriodDomain periodDomain = periodDao.queryCurrentPeriod();

		collectionFileDomain.setBank(bankDomain);
		collectionFileDomain.setPeriod(periodDomain);
		collectionFileDomain.setOutTime(this.getDate());
		collectionFileDomain.setOutMan(createor);
		//从配置文件uploadfile获取出盘文件路径
		collectionFileDomain.setOutFilePath(getPath(OUTFILEPATH));
		collectionFileDomain.setOutFileName(buildFilename(collectionFileDomain.getBank(),periodDomain));
		return (CollectionFileDomain)bankCollectionDao.saveObject(collectionFileDomain);
	}

	/**
	 * 功能描述:取得解析后的路径。<br>
	 * @param key
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-9<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String getPath(String key){
		return UpLoadFile.parsePath(UpLoadFile.getProperty(key));
	}

	/**
	 * 功能描述:创建出盘文件名(在FYSZ006划款银行设置功能点中定义的）文件名起头＋年月日＋四位流水号.例如：BC200708010001.XLS<br>
	 * 目前还存在一个问题：管理区编号无法获得,这个需要等权限加进来后在处理。
	 * @param bankDomain
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-24  <br>
	 * @throws ParseException
	 */
	private String buildFilename(BankDomain bankDomain,PeriodDomain periodDomain) throws ParseException{
		String fileName = buildFlowNumber(bankDomain.getOutFilePrefix(),periodDomain.getChargePrecinct().getKeyword(),buildSequence());
		super.logger.info("BankCollection out fileName" + fileName);
		if(!fileName.endsWith(".xls"))
			fileName = fileName + ".xls";
		return fileName;
	}
	/**
	 * 功能描述:给所有需要托收的子帐户添加托收明细<br>
	 * @param chargeAccountList
	 * Create author:yixianjian <br>
	 * Create on:2008-9-24  <br>
	 */
	@SuppressWarnings("unchecked")
	private List<CollecDetail> addCollecDetail(CollectionFileDomain collectionFileDomain,
								 List<Object[]> chargeAccountList){
		List<CollecDetail> collecDetailList = new ArrayList<CollecDetail>();
//		每项为一个Object数组,1-房屋ID,2--房号,3--产权ID,4--银行账号,5--开户人,6--子帐户余额
		if(chargeAccountList != null){
			for(Iterator<Object[]> iterator = chargeAccountList.iterator(); iterator.hasNext(); ){
				Object[] array = iterator.next();
				CollecDetail collecDetail = new CollecDetail();
				//设置房屋ID, 产权ID 来自帐户表
				collecDetail.setHouse(houseDao.findById(((BigInteger)array[0]).longValue()));

				ProRightDO proRightDO = new ProRightDO();
				proRightDO.setProRightId(Long.valueOf(array[2].toString()));
				collecDetail.setProRight(proRightDO);

				//设置帐户号码,开户人  来自银行划款帐号
				collecDetail.setAccNumber(array[3].toString());
				collecDetail.setAccOwner(array[4].toString());
				//设置余额  来自物业子帐户
				collecDetail.setAmt((BigDecimal)array[5]);
				//设置状态为新建状态
				collecDetail.setStatuz("001");
				collecDetail.setCollecFile(collectionFileDomain);
				collecDetailList.add(collecDetail);
			}
		}
		bankCollectionDao.doSaveAll(collecDetailList);
		return collecDetailList;
	}
	/**
	 * 功能描述: 更新子帐户交易, 将所有收费类型的未缴费的记录更新为出盘锁定状态，同时在托收明细和子帐户交易关系表中插入关系表中。<br>
	 * @param chargeAccountList
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-24  <br>
	 */
	private void updateTransaction(Long bankId,Long collectionId){
		//跟新所有出盘子帐户交易状态为出盘锁定
		debitTransDao.updateDEbitTransDomainToOut(bankId);
		//建立出盘明细同子帐户交易的关系
		debitTransDao.insertDetailRTrans(collectionId);

	}
	/**
	 * 功能描述:  根据房屋ID、产权ID匹配交易对应的托收明细<br>
	 * @param debitTrans
	 * @param collecDetaillist
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-25  <br>
	 */
	private CollecDetail queryCollecDetailByDebitTrans(DebitTransDomain debitTrans,List<CollecDetail> collecDetaillist){
		if(debitTrans == null) return null;
		if(collecDetaillist == null) return null;

		boolean flag = false;
		for(int i = 0; i < collecDetaillist.size(); i++){
			flag = debitTrans.getBindingAccount().getAccount().getHouse().getKeyword().equals(collecDetaillist.get(i).getHouse().getKeyword());
			flag = flag && debitTrans.getBindingAccount().getAccount().getProRight().getProRightId().equals(collecDetaillist.get(i).getProRight().getProRightId());
			if(flag)
				return collecDetaillist.get(i);
		}
		return null;
	}
	/**
	 * 功能描述: 生成流水号:<br>
	 * 流水号生成规则:每天开始时,流水号从0001开始,每次加1
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-24  <br>
	 * @throws ParseException
	 */
	private  int buildSequence() throws ParseException{
		int sequence = bankCollectionDao.getMaxOutFileNameSeq();
		return ++sequence;
	}
	public static void main(String[] args){
		int result = DateUtil.getCurrDateStr("yyyyMMdd").compareTo("20070809");
		System.out.println(DateUtil.getCurrDateStr("yyyyMMdd"));
		System.out.println(result);
	}


	/**
	 * 功能描述:批量将Domain转换为VO<br>
	 * @param domainlist
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-25  <br>
	 */
	private List<CollectionFileVO> convertDoToVO(List<CollectionFileDomain> domainlist){
		if(domainlist == null || domainlist.size() == 0) return null;
		List<CollectionFileVO> voList = new ArrayList<CollectionFileVO>();
		for(Iterator<CollectionFileDomain> iterator = domainlist.iterator(); iterator.hasNext();){
			voList.add(convertDoToVO(iterator.next()));
		}
		return voList;
	}
	/**
	 * 功能描述:转换单个domain为VO<br>
	 * @param domain
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-25  <br>
	 */
	private CollectionFileVO convertDoToVO(CollectionFileDomain domain){
		if(domain == null) return null;
		CollectionFileVO vo = new CollectionFileVO();
		vo.setBankId(domain.getBank().getKeyword());
		vo.setBankName(domain.getBank().getBankName());
		vo.setInFileName(domain.getInFileName());
		vo.setInFilePath(domain.getInFilePath());
		vo.setInMan(domain.getInMan());
		vo.setInTime(domain.getInTime());
		vo.setKeyword(domain.getKeyword());
		vo.setOutFileName(domain.getOutFileName());
		vo.setOutFilePath(domain.getOutFilePath());
		vo.setOutMan(domain.getOutMan());
		vo.setOutTime(domain.getOutTime());
		vo.setPeriod(domain.getPeriod().getPeriod());
		vo.setPeriodId(domain.getPeriod().getKeyword());

		return vo;
	}

	/**
	 * 功能描述: 创建银行出盘文件<br>
	 * @param collectionFileDomain
	 * Create author:yixianjian <br>
	 * Create on:2008-10-6  <br>
	 * @throws ParseException
	 * @throws Exception
	 */
	private void buildOutFile(CollectionFileDomain collectionFileDomain,List<CollecDetail> collecDetaillist) throws ParseException {
		HSSFWorkbook hssfWorkBook = new HSSFWorkbook();//创建文档
		HSSFSheet  hssfWorkSheet = hssfWorkBook.createSheet();
		buildOutExcelHead(hssfWorkSheet);  //创建表头
		buildOutExcelRow(hssfWorkSheet,collecDetaillist);//创建记录行

		writeHSSFWorkbook(collectionFileDomain.getOutFilePath(),collectionFileDomain.getOutFileName(),hssfWorkBook);
	}
	/**
	 * 功能描述:创建excel表头.id,房号,姓名,帐户号码,金额,划款状态<br>
	 * @param hssfWorkSheet
	 * Create author:yixianjian <br>
	 * Create on:2008-10-6  <br>
	 */
	@SuppressWarnings("deprecation")
	private void buildOutExcelHead(HSSFSheet  hssfWorkSheet){
		String[] headStr = {"id","房号","姓名","帐户号码","金额","划款状态(Y/N)"};
		HSSFRow  row = hssfWorkSheet.createRow(0);
		for(int i = 0 ; i < headStr.length; i++){
			 HSSFCell cell = row.createCell((short)i);
			 cell.setCellValue(headStr[i]);
		}
	}
	/**
	 * 功能描述:创建记录行<br>
	 * @param hssfWorkSheet  excel sheet
	 * @param collecDetaillist  需要添加到记录行的交易明细
	 * Create author:yixianjian <br>
	 * Create on:2008-10-6  <br>
	 */
	private void buildOutExcelRow(HSSFSheet hssfWorkSheet,List<CollecDetail> collecDetaillist){
		int count = 1; //标识到了那行了,创建记录行时,需要指定行数,行数没加一次自动++
		HSSFCell cell = null;
		for(Iterator<CollecDetail> iterator = collecDetaillist.iterator(); iterator.hasNext();){
			CollecDetail collecDetail = iterator.next();
			HSSFRow row = hssfWorkSheet.createRow(count++);
			if(count > 4000)
				System.out.println("TEST");
			//ID
			cell = row.createCell((short)0);
			cell.setCellValue(new HSSFRichTextString(collecDetail.getCollecDetailId().toString()));

			//房号
			cell = row.createCell((short)1);
			cell.setCellValue(new HSSFRichTextString(collecDetail.getHouse().getHouseNumber()));

			//姓名
			cell = row.createCell((short)2);
			cell.setCellValue(new HSSFRichTextString(collecDetail.getAccOwner()));

			//帐户号码
			cell = row.createCell((short)3);
			cell.setCellValue(new HSSFRichTextString(collecDetail.getAccNumber()));

			//金额
			cell = row.createCell((short)4);
			NumberFormat numFormat = NumberFormat.getInstance();
			numFormat.setMinimumFractionDigits(2);
			numFormat.setMaximumFractionDigits(2);
			numFormat.setGroupingUsed(false);
			cell.setCellValue(new HSSFRichTextString(numFormat.format(collecDetail.getAmt())));
		}
	}

	/**
	 * 功能描述:获取保存文件的目录,如果目录不存在,则自动创建<br>
	 * @param filePath
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-10-7  <br>
	 * @throws IOException
	 */
	private String getOutFileDir(String filePath) throws IOException{
//		String filePath = collectionFileDomain.getOutFilePath();
//		String replace = File.separator.equals("\\") ? "\\\\" : "/";  //获取可替换的字符部分  由于java正则表达式处理方式决定要求这么做
//		filePath = filePath.replaceAll("\\\\", replace);//处理window的路径分隔符问题
//		filePath = filePath.replaceAll("/", replace); //处理unix的路径分隔符
//		filePath = filePath.endsWith(File.separator)? filePath : filePath + File.separator;//最后加上一个分隔符

		File file = new File(filePath);
		if(!file.exists())
			file.mkdirs();
		return filePath;
	}

	/**
	 * 功能描述:把生成的excel表格保存到指定路径<br>
	 * @param hssfWorkBook
	 * Create author:yixianjian <br>
	 * Create on:2008-10-6  <br>
	 * @throws Exception
	 */
	private void writeHSSFWorkbook(String filePath,String fileName,HSSFWorkbook hssfWorkBook){
		try {
			String filePath1 = getOutFileDir(filePath);
			//将excel中的内容写入文件.经测试,在window xp2下如果文件不存在则自动创建
//			String fileName = collectionFileDomain.getOutFileName();
			StringBuffer file = new StringBuffer().append(filePath1).append(fileName); //获取完整的文件名
			OutputStream outputStream = new FileOutputStream(file.toString());
			hssfWorkBook.write(outputStream);
			outputStream.flush();
		} catch (Exception e) {
			throw new BusinessException("写入文件失败");
		}
	}

	private List<CollectionDetailVO> convertDetailDoToVoList(List<CollecDetail> collecDetailDoList){
		List<CollectionDetailVO> collectionDetailVOList=new ArrayList<CollectionDetailVO>();
		for(CollecDetail item : collecDetailDoList){
			collectionDetailVOList.add(convertDetailDoToVO(item));
		}
		return collectionDetailVOList;
	}

	private CollectionDetailVO convertDetailDoToVO(CollecDetail collecDetailDo){
		CollectionDetailVO collectionDetailVO=new CollectionDetailVO();
		collectionDetailVO.setHouseNum(collecDetailDo.getHouse().getHouseNumber());
		String clients=houseDao.getClientsByHouseId(collecDetailDo.getHouse().getKeyword());
		collectionDetailVO.setClients(clients);
		collectionDetailVO.setAccOwner(collecDetailDo.getAccOwner());
		collectionDetailVO.setAccNumber(collecDetailDo.getAccNumber());
		collectionDetailVO.setAmt(collecDetailDo.getAmt());
		collectionDetailVO.setStatus(collecDetailDo.getStatuz());
		return collectionDetailVO;
	}

	/**
	 * 功能描述:  判断参数不能为空<br>
	 * Create author:yixianjian <br>
	 * Create on:2008-9-23  <br>
	 */
	private void checkParamNotNum(Object param,String exceptionMessage){
		if(param == null)
			throw new BizLayerException(exceptionMessage);
	}
	public void setBankCollectionDao(IBankCollectionDao bankCollectionDao) {
		this.bankCollectionDao = bankCollectionDao;
	}

	public void setSubAccountDao(ISubAccountDao subAccountDao) {
		this.subAccountDao = subAccountDao;
	}

	public void setFeeBankDao(IFeeBankDao feeBankDao) {
		this.feeBankDao = feeBankDao;
	}

	public void setPeriodDao(IPeriodDao periodDao) {
		this.periodDao = periodDao;
	}

	public void setDebitTransDao(IDebitTransDao debitTransDao) {
		this.debitTransDao = debitTransDao;
	}
	public void setBusinessCodeDao(IBusinessCodeDao businessCodeDao) {
		this.businessCodeDao = businessCodeDao;
	}
	public void setChargeVoucherDao(IChargeVoucherDao chargeVoucherDao) {
		this.chargeVoucherDao = chargeVoucherDao;
	}
	public void setCollecDetailDao(ICollecDetailDao collecDetailDao) {
		this.collecDetailDao = collecDetailDao;
	}
	public void setTransactionDao(ITransactionDao transactionDao) {
		this.transactionDao = transactionDao;
	}
	public void setTransactionRelationDao(
			ITransactionRelationDao transactionRelationDao) {
		this.transactionRelationDao = transactionRelationDao;
	}
	public void setHouseDao(IHouseDao houseDao) {
		this.houseDao = houseDao;
	}

}
